﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AscendEast_DataEntry
{
    public partial class Form1 : Form
    {
        OleDbConnection dbconn;
        OleDbCommand dbcmd = new OleDbCommand();
        string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\db.mdb;Persist Security Info=False";
        DataAccess dal = new DataAccess();
        string state_code;
        string state_name;
        string school_name;
        string centre_code;
        string centre_name;

        string select_states_query = "select * from tbl_states";
        string select_schools_query = "select * from tbl_schools";
        //string select_centres_query = "select * from tbl_centres";
        string select_centres_query = "select c.centre_code,c.centre_name,s.state_name from tbl_centres c inner join tbl_states s on c.state_code = s.state_code";


        public Form1()
        {
            InitializeComponent();
            PopulateDropDown_States_add();
            PopulateDropDown_States_view();
            PopulateDropDown_Years();
            PopulateDropDown_centres();
            PopulauteComboBox_Schools();
            PopulateDropDown_classes();
            tb_roll_student_add.Enabled = false;
        }


        private void PopulateDropDown_Years()
        {
            string select_years_query = "select * from tbl_years";
            DataTable dt0 = dal.Select(select_years_query);
            DataTable dt = new DataTable();
            dt.Columns.Add("years", typeof(string));
            dt.Rows.Add("Select Year");
            for (int i = 0; i < dt0.Rows.Count; i++)
            {
                dt.Rows.Add(dt0.Rows[i][0]);
            }

            cb_year_student_add.DisplayMember = dt.Columns["years"].ToString();
            cb_year_student_add.ValueMember = dt.Columns["years"].ToString();
            cb_year_student_add.DataSource = dt;
        }

        private void PopulateDropDown_classes()
        {
            string select_classes_query = "select * from tbl_classes";
            DataTable dt0 = dal.Select(select_classes_query);
            DataTable dt = new DataTable();
            dt.Columns.Add("class_id", typeof(String));
            dt.Columns.Add("class_name", typeof(String));
            dt.Rows.Add("null", "Select Class");
            for (int i = 0; i < dt0.Rows.Count; i++)
            {
                dt.Rows.Add(dt0.Rows[i][0], dt0.Rows[i][1]);
            }

            cb_class_student_add.DisplayMember = dt.Columns["class_name"].ToString();
            cb_class_student_add.ValueMember = dt.Columns["class_id"].ToString();
            cb_class_student_add.DataSource = dt;
        }


        private void PopulateDropDown_centres()
        {
            string select_centres_query = "select centre_code,centre_name from tbl_centres";
            DataTable dt0 = dal.Select(select_centres_query);
            DataTable dt = new DataTable();
            dt.Columns.Add("centre_code", typeof(String));
            dt.Columns.Add("centre_name", typeof(String));
            //dt.Columns.Add("state_code", typeof(String));
            dt.Rows.Add("null", "Select Centre");
            for (int i = 0; i < dt0.Rows.Count; i++)
            {
                dt.Rows.Add(dt0.Rows[i][0], dt0.Rows[i][1]);
            }

            cb_centre_student_add.DisplayMember = dt.Columns["centre_name"].ToString();
            cb_centre_student_add.ValueMember = dt.Columns["centre_code"].ToString();
            cb_centre_student_add.DataSource = dt;
        }

        private void PopulauteComboBox_Schools()
        {
            string select_schools_query = "select * from tbl_schools";
            DataTable dt0 = dal.Select(select_schools_query);
            DataTable dt = new DataTable();
            dt.Columns.Add("school_id", typeof(String));
            dt.Columns.Add("school_name", typeof(String));
            dt.Rows.Add("null", "Select School");
            for (int i = 0; i < dt0.Rows.Count; i++)
            {
                dt.Rows.Add(dt0.Rows[i][0], dt0.Rows[i][1]);
            }

            cb_school_student_add.DisplayMember = dt.Columns["school_name"].ToString();
            cb_school_student_add.ValueMember = dt.Columns["school_id"].ToString();
            cb_school_student_add.DataSource = dt;
        }

        private void PopulateDropDown_States_add()
        {
            string select_states_query = "select * from tbl_states";
            DataTable dt0 = dal.Select(select_states_query);
            DataTable dt = new DataTable();
            dt.Columns.Add("state_code", typeof(String));
            dt.Columns.Add("state_name", typeof(String));
            dt.Rows.Add("null", "Select State");
            for (int i = 0; i < dt0.Rows.Count; i++)
            {
                dt.Rows.Add(dt0.Rows[i][0], dt0.Rows[i][1]);
            }

            cb_states_centre_add.DisplayMember = dt.Columns["state_name"].ToString();
            cb_states_centre_add.ValueMember = dt.Columns["state_code"].ToString();
            cb_states_centre_add.DataSource = dt;
        }

        private void PopulateDropDown_States_view()
        {
            string select_states_query = "select * from tbl_states";
            DataTable dt0 = dal.Select(select_states_query);
            DataTable dt = new DataTable();
            dt.Columns.Add("state_code", typeof(String));
            dt.Columns.Add("state_name", typeof(String));
            dt.Rows.Add("null", "Select State");
            for (int i = 0; i < dt0.Rows.Count; i++)
            {
                dt.Rows.Add(dt0.Rows[i][0], dt0.Rows[i][1]);
            }

            cb_states_centre_view.DisplayMember = dt.Columns["state_name"].ToString();
            cb_states_centre_view.ValueMember = dt.Columns["state_code"].ToString();
            cb_states_centre_view.DataSource = dt;
        }

        private void btn_submit_state_add_Click(object sender, EventArgs e)
        {
            if (tb_state_code_state_add.Text == "")
                MessageBox.Show("Please Insert State Code");
            else if (tb_state_name_state_add.Text == "")
                MessageBox.Show("Please Insert State Name");

            else
            {
                state_code = tb_state_code_state_add.Text;
                state_name = tb_state_name_state_add.Text;

                bool duplicate_flag = dal.CheckDuplicate("tbl_states", "state_code", state_code);
                if (duplicate_flag == true)
                {
                    MessageBox.Show("State already exists. Please enter another State.");
                    state_code = String.Empty;
                    state_name = String.Empty;
                    tb_state_name_state_add.Text = String.Empty;
                    tb_state_code_state_add.Text = String.Empty;
                }
                else
                {
                    String insertquery = "insert into tbl_states(state_code,state_name) values ('" + state_code + "','" + state_name + "')";

                    dbconn = new OleDbConnection(constr);
                    dbcmd.Connection = dbconn;
                    dbcmd.CommandType = CommandType.Text;
                    dbcmd.CommandText = insertquery;
                    dbcmd.Connection.Open();
                    int temp2 = dbcmd.ExecuteNonQuery();
                    dbcmd.Connection.Close();
                    if (temp2 > 0)
                    {
                        MessageBox.Show("Record Inserted Successfully");
                        PopulateDropDown_States_add();
                    }
                    else
                        MessageBox.Show("Try again");

                    state_code = String.Empty;
                    state_name = String.Empty;
                    tb_state_name_state_add.Text = String.Empty;
                    tb_state_code_state_add.Text = String.Empty;
                }
            }



        }

        private void btn_view_states_states_Click(object sender, EventArgs e)
        {
            PopulateDataGridView(select_states_query);
        }

        private void PopulateDataGridView(string query)
        {
            dgv_states.DataSource = null;
            dgv_states.Rows.Clear();
            dgv_states.Refresh();

            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, constr);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            //DataTable dataTable = new DataTable();
            DataSet ds = new DataSet();

            dAdapter.Fill(ds);

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                dgv_states.Rows.Add(ds.Tables[0].Rows[i][0], ds.Tables[0].Rows[i][1]);
            }
        }

        private void btn_cancel_states_Click(object sender, EventArgs e)
        {
            state_code = String.Empty;
            state_name = String.Empty;
            tb_state_name_state_add.Text = String.Empty;
            tb_state_code_state_add.Text = String.Empty;
        }

        private void btn_edit_states_Click(object sender, EventArgs e)
        {
            Form_State_Edit edit_state_form = new Form_State_Edit();
            edit_state_form.ShowDialog();
            PopulateDropDown_States_add();
            PopulateDropDown_States_view();
            PopulateDataGridView(select_states_query);

        }

        private void btn_delete_states_Click(object sender, EventArgs e)
        {
            Form_State_Delete delete_state_form = new Form_State_Delete();
            delete_state_form.ShowDialog();
            PopulateDropDown_States_add();
            PopulateDropDown_States_view();
            PopulateDataGridView(select_states_query);
        }

        private void btn_add_school_add_Click(object sender, EventArgs e)
        {
            if (tb_school_name_add.Text == "")
                MessageBox.Show("Please enter a School Name");
            else
            {
                school_name = tb_school_name_add.Text;
                string check_duplicate_query = "select * from tbl_schools where school_name = '" + school_name + "'";
                string add_school_query = "insert into tbl_schools(school_name) values ('" + school_name + "')";
                bool duplicate_flag = dal.Check_Duplicate(check_duplicate_query);
                if (duplicate_flag == true)
                {
                    MessageBox.Show("School already exists. Please enter another School.");
                    tb_school_name_add.Text = string.Empty;
                    school_name = string.Empty;
                }
                else
                {
                    bool inserted_flag = dal.Insert(add_school_query);
                    if (inserted_flag == true)
                        MessageBox.Show("School successfully added.");
                    else
                        MessageBox.Show("Try again.");

                    school_name = string.Empty;
                    tb_school_name_add.Text = string.Empty;
                }
            }
        }

        private void btn_view_schools_Click(object sender, EventArgs e)
        {
            PopulateGridView_Schools();
        }

        private void PopulateGridView_Schools()
        {
            dgv_schools_view.DataSource = null;
            dgv_schools_view.Rows.Clear();
            dgv_schools_view.Refresh();
            DataTable dt = new DataTable();
            dt = dal.Select(select_schools_query);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dgv_schools_view.Rows.Add(dt.Rows[i][1]);
            }
        }

        private void btn_edit_school_Click(object sender, EventArgs e)
        {
            Form_School_Edit edit_school_form = new Form_School_Edit();
            edit_school_form.ShowDialog();
            PopulateGridView_Schools();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Form_School_Delete delete_school_form = new Form_School_Delete();
            delete_school_form.ShowDialog();
            PopulateGridView_Schools();
        }

        private void btn_submit_centres_add_Click(object sender, EventArgs e)
        {
            if (tb_centre_name_add.Text == "")
            {
                MessageBox.Show("Please enter Centre Name");
                tb_centre_name_add.Focus();
            }
            else if (tb_centre_code_add.Text == "")
            {
                MessageBox.Show("Please enter Centre Code");
                tb_centre_code_add.Focus();
            }
            else if (cb_states_centre_add.SelectedIndex == 0)
                MessageBox.Show("Please select any State");
            else
            {
                centre_name = tb_centre_name_add.Text;
                centre_code = tb_centre_code_add.Text;
                state_code = cb_states_centre_add.SelectedValue.ToString();
                string check_duplicate_query = "select * from tbl_centres where centre_code = '" + centre_code + "'";
                string add_centre_query = "insert into tbl_centres(centre_code,centre_name,state_code) values ('" + centre_code + "','" + centre_name + "','" + state_code + "')";
                bool duplicate_flag = dal.Check_Duplicate(check_duplicate_query);
                if (duplicate_flag == true)
                {
                    MessageBox.Show("Centre already exists. Please enter another Centre.");
                    tb_centre_name_add.Text = string.Empty;
                    tb_centre_code_add.Text = string.Empty;
                    cb_states_centre_add.SelectedIndex = 0;
                    centre_name = string.Empty;
                    centre_code = string.Empty;
                    state_code = string.Empty;
                }
                else
                {
                    bool inserted_flag = dal.Insert(add_centre_query);
                    if (inserted_flag == true)
                        MessageBox.Show("Centre successfully added.");
                    else
                        MessageBox.Show("Try again.");

                    tb_centre_name_add.Text = string.Empty;
                    tb_centre_code_add.Text = string.Empty;
                    cb_states_centre_add.SelectedIndex = 0;
                    centre_name = string.Empty;
                    centre_code = string.Empty;
                    state_code = string.Empty;
                }
            }
        }

        private void btn_cancel_centre_add_Click(object sender, EventArgs e)
        {
            tb_centre_name_add.Text = string.Empty;
            tb_centre_code_add.Text = string.Empty;
            cb_states_centre_add.SelectedIndex = 0;
        }

        private void btn_submit_centres_view_Click(object sender, EventArgs e)
        {
            if (cb_states_centre_view.SelectedIndex == 0)
            {
                PopulateGridView_Centres(select_centres_query);
            }
            else
            {
                state_code = cb_states_centre_view.SelectedValue.ToString();
                string select_centre_in_state_query = "select * from tbl_centres where state_code = '" + state_code + "'";
                PopulateGridView_Centres(select_centre_in_state_query);
            }
        }

        private void PopulateGridView_Centres(string query)
        {
            dgv_centres_view.DataSource = null;
            dgv_centres_view.Rows.Clear();
            dgv_centres_view.Refresh();
            DataTable dt = new DataTable();
            dt = dal.Select(query);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                dgv_centres_view.Rows.Add(dt.Rows[i][0], dt.Rows[i][1], dt.Rows[i][2]);
            }
        }

        private void btn_edit_centres_Click(object sender, EventArgs e)
        {
            Form_Centre_Edit edit_centre_form = new Form_Centre_Edit();
            edit_centre_form.ShowDialog();
            PopulateGridView_Centres(select_centres_query);
        }

        private void btn_delete_centres_Click(object sender, EventArgs e)
        {
            Form_Centre_Delete delete_centre_form = new Form_Centre_Delete();
            delete_centre_form.ShowDialog();
            PopulateGridView_Centres(select_centres_query);
        }

        private void cb_centre_student_add_SelectedIndexChanged(object sender, EventArgs e)
        {
            tb_roll_student_add.Text = cb_centre_student_add.SelectedValue.ToString();
        }

        private void btn_submit_student_add_Click(object sender, EventArgs e)
        {
            if (cb_year_student_add.SelectedIndex == 0)
            {
                MessageBox.Show("Please select the Year");
                cb_year_student_add.Focus();
            }
            else if (cb_centre_student_add.SelectedIndex == 0)
            {
                MessageBox.Show("Please select the Centre");
                cb_centre_student_add.Focus();
            }

            else if (cb_class_student_add.SelectedIndex == 0)
            {
                MessageBox.Show("Please select a Class");
                cb_class_student_add.Focus();
            }
            else if (tb_firstname_student_add.Text == "")
            {
                MessageBox.Show("Please enter First Name");
                tb_firstname_student_add.Focus();
            }
            else if (tb_surname_student_add.Text == "")
            {
                MessageBox.Show("Please enter Surname");
                tb_surname_student_add.Focus();
            }
            else if (cb_school_student_add.SelectedIndex == 0)
            {
                MessageBox.Show("Please select a School");
                cb_school_student_add.Focus();
            }
            else if (tb_no_student_add.Text == "")
            {
                MessageBox.Show("Please enter Roll Number");
                tb_no_student_add.Focus();
            }
            else if (tb_a_student_add.Text == "")
            {
                MessageBox.Show("Please enter Marks for A");
                tb_a_student_add.Focus();
            }
            else if (tb_b_student_add.Text == "")
            {
                MessageBox.Show("Please enter Marks for B");
                tb_b_student_add.Focus();
            }
            else if (tb_c_student_add.Text == "")
            {
                MessageBox.Show("Please enter Marks for C");
                tb_c_student_add.Focus();
            }
            else if (tb_d_student_add.Text == "")
            {
                MessageBox.Show("Please enter Marks for D");
                tb_d_student_add.Focus();
            }
            else if (tb_e_student_add.Text == "")
            {
                MessageBox.Show("Please enter Marks for E");
                tb_e_student_add.Focus();
            }

            else
            {
                int year = int.Parse(cb_year_student_add.SelectedValue.ToString());
                string centre = cb_centre_student_add.SelectedValue.ToString();
                int class_id = int.Parse(cb_class_student_add.SelectedValue.ToString());
                string first_name = tb_firstname_student_add.Text;
                string middle_name = tb_middlename_student_add.Text;
                string last_name = tb_surname_student_add.Text;
                int school = int.Parse(cb_school_student_add.SelectedValue.ToString());
                string roll = tb_roll_student_add.Text;
                int number = int.Parse(tb_no_student_add.Text);
                int a = int.Parse(tb_a_student_add.Text);
                int b = int.Parse(tb_b_student_add.Text);
                int c = int.Parse(tb_c_student_add.Text);
                int d = int.Parse(tb_d_student_add.Text);
                int f = int.Parse(tb_e_student_add.Text);

                string student_id = roll + number.ToString();

                string check_duplicate_query = "select * from tbl_students where student_id = '" + student_id + "'";
                string add_student_query = "insert into tbl_students(student_id,first_name,middle_name,last_name,roll,number,centre_code,school_id,class_id,A,B,C,D,E,years) values('"
                + student_id + "','" + first_name + "','" + middle_name + "','" + last_name + "','" + roll + "'," + number + ",'" + centre + "'," + school + "," + class_id + "," + a + "," + b + "," + c + "," + d + "," + f + "," + year + ")";
                bool duplicate_flag = dal.Check_Duplicate(check_duplicate_query);
                if (duplicate_flag == true)
                {
                    MessageBox.Show("Student already exists.");

                    Reset_Student_add();
                }
                else
                {
                    bool inserted_flag = dal.Insert(add_student_query);
                    if (inserted_flag == true)
                        MessageBox.Show("Student successfully added.");
                    else
                        MessageBox.Show("Try again.");

                    Reset_Student_add();
                }
            }
        }

        public void Reset_Student_add()
        {
            cb_year_student_add.SelectedIndex = 0;
            cb_year_student_add.SelectedIndex = 0;
            cb_centre_student_add.SelectedIndex = 0;
            cb_class_student_add.SelectedIndex = 0;
            cb_school_student_add.SelectedIndex = 0;

            tb_firstname_student_add.Text = string.Empty;
            tb_middlename_student_add.Text = string.Empty;
            tb_surname_student_add.Text = string.Empty;
            tb_no_student_add.Text = string.Empty;
            tb_a_student_add.Text = string.Empty;
            tb_b_student_add.Text = string.Empty;
            tb_c_student_add.Text = string.Empty;
            tb_d_student_add.Text = string.Empty;
            tb_e_student_add.Text = string.Empty;
        }

        private void btn_cancel_student_add_Click(object sender, EventArgs e)
        {
            Reset_Student_add();
        }
    }
}
